package com.um.springboot.starter.orm.dao;

import com.um.springboot.starter.config.SystemSQL;
import com.um.springboot.starter.dao.CommonDao;
import com.um.springboot.starter.entity.Page;
import com.um.springboot.starter.logs.SqlLog;
import com.um.springboot.starter.orm.entity.RangeEntity;
import com.um.springboot.starter.orm.entity.SqlRange;
import com.um.springboot.starter.utils.NameUtils;
import com.um.springboot.starter.utils.ReflectUtil;
import com.um.springboot.starter.utils.StringUtil;
import com.um.springboot.starter.utils.condition.ConditionBuilder;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.util.*;

/**
 * 实体操作 dao父类
 * @author 邓南辉
 * 日期 2021/4/10T
 * 版权所有 广州优匠科技有限公司
 */
@Slf4j
public abstract class BaseEntityDao<T>{

    /** 具体操作的实体类对象 */
    private Class<T> entityClass;

    /** 名称加工处理器 */
    private NameHandler nameHandler;

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    @Autowired
    protected NamedParameterJdbcTemplate npJdbcTemplate;

    @Autowired
    CommonDao commonDao;

    @Autowired
    SqlLog sqlLog;


    /**
     * 构造方法，获取运行时的具体实体对象
     */
    public BaseEntityDao()
    {
        Type superclass = getClass().getGenericSuperclass();
        ParameterizedType type = (ParameterizedType) superclass;
        entityClass = (Class<T>) type.getActualTypeArguments()[0];
        getActualNameHandler();
    }

    /**
     * 获取实际运行时的名称处理器
     *
     * @return
     */
    private NameHandler getActualNameHandler()
    {
        if (nameHandler == null)
        {
            synchronized (this)
            {
                if (nameHandler == null)
                {
                    nameHandler = this.getNameHandler();
                }
            }
        }
        return nameHandler;
    }

    /**
     * 得到名称处理器，子类覆盖此方法实现自己的名称转换处理器
     *
     * @return
     */
    protected NameHandler getNameHandler()
    {
        return new DefaultNameHandler();
    }


    /**
     * 返回表名称
     * @return
     */
    public String getTableName(){
        return nameHandler.getTableName(this.entityClass);
    }

    /**
     * 返回主键名称
     * @return
     */
    public String getPrimaryName(){
        return nameHandler.getPrimaryName(this.entityClass);
    }

    /**
     * 返回主键属性名称
     *
     * @return
     */
    public String getPrimaryPropertyName() {
        return NameUtils.getPropertyName(getPrimaryName());
    }

    /**
     * 插入一个实体,饶开框架，不做历史记录
     * @param entity
     * @return
     */
    public boolean addRecord(T entity){
        return commonDao.addRecord(getTableName(),entity);
    }

    /**
     * 更新一个实体,饶开框架，不做历史记录
     * @param entity
     * @return
     */
    public boolean updateRecord(T entity){
        return commonDao.updateRecord(getTableName(),entity);
    }

    /**
     * 保存实体，饶开框架，不做历史记录
     */
    public boolean saveRow(T entity) {
        String id = (String) ReflectUtil.getValue(entity, getPrimaryPropertyName());
        if (!StringUtil.getTrim(id).equals("")) {
            return updateRow(entity);
        } else {
            ReflectUtil.setValue(entity, getPrimaryPropertyName(), UUID.randomUUID().toString().replaceAll("-", ""));
            return addRow(entity);
        }
    }

    /**
     * 插入一个实体,饶开框架，不添加com_id
     * @param entity
     * @return
     */
    public boolean addRow(T entity){
        return commonDao.addRow(getTableName(),entity);
    }

    /**
     * 更新一个实体,饶开框架，不添加com_id
     * @param entity
     * @return
     */
    public boolean updateRow(T entity){
        return commonDao.updateRow(getTableName(),entity);
    }

    /**
     * 按主键删除实体，饶开框架，不添加com_id
     * @param id
     * @return
     */
    public boolean deleteRow(String id){
        return deleteRow(getPrimaryName(),id);
    }

    /**
     * 根据单个属性删除实体，饶开框架，不添加com_id
     * @param property
     * @param value
     * @return
     */
    public boolean deleteRow(String property,Object value){
        return deleteRow(new String[]{property},new Object[]{value});
    }

    /**
     * 根据多个属生查询实体，饶开框架，不添加com_id
     * @param properties
     * @param values
     * @return
     */
    public boolean deleteRow(String[] properties,Object[] values){
        return commonDao.deleteRow(getTableName(),properties,values);
    }

    /**
     * 根据主键查询实体，饶开框架，不添加com_id
     * @param id
     * @return
     */
    public T getRow(String id){
        return getRow(getPrimaryName(),id);
    }

    /**
     * 根据单个属性查询实体，饶开框架，不添加com_id
     * @param property
     * @param value
     * @return
     */
    public T getRow(String property,Object value){
        return getRow(new String[]{property},new Object[]{value});
    }

    /**
     * 根据多个属生查询实体，饶开框架，不添加com_id
     * @param properties
     * @param values
     * @return
     */
    public T getRow(String[] properties,Object[] values){
        String sql = "select * from " + getTableName() + " where is_delete = '0' ";

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }
        sqlLog.debug("getRow",sql,values);
        List<T> list = (List<T>)jdbcTemplate.query(sql,new DefaultRowMapper(entityClass, this.getActualNameHandler()),values);
        if(list.size() > 0){
            return list.get(0);
        }else{
            return null;
        }
    }

    /**
     * 根据多个属生查询实体，饶开框架，不添加com_id
     * @param properties
     * @param values
     * @return
     */
    public List<T> getRows(String[] properties,Object[] values,String[] orderNames,String[] sortOns){
        String sql = "select * from " + getTableName() + " where is_delete = '0' ";

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }

        if(orderNames.length > 0){
            int start=0;
            for(int index = 0;index < orderNames.length;index ++){

                Object property = orderNames[index];
                if(property == null || property.equals(""))
                    continue;

                if(start == 0)
                    sql += " order by ";

                if(start > 0)
                    sql += " , ";

                sql += property +" "+sortOns[index];
                start ++;
            }
        }

        sqlLog.debug("getRows",sql,values);
        List<T> list = (List<T>)jdbcTemplate.query(sql,new DefaultRowMapper(entityClass, this.getActualNameHandler()),values);
        return list;
    }

    /**
     * 插入一个实体
     * @param entity
     * @return
     */
    public boolean add(T entity){
        return commonDao.add(getTableName(),entity);
    }

    /**
     * 插入一个实体,返回自增ID
     * @param entity
     * @return
     */
    public Long insert(T entity){
        return commonDao.insert(getTableName(),entity);
    }

    /**
     * 插入一个实体
     * @param entity
     * @return
     */
    public boolean save(T entity){
        String id = (String) ReflectUtil.getValue(entity, getPrimaryPropertyName());
        if (!StringUtil.getTrim(id).equals("")) {
            return commonDao.update(getTableName(),entity);
        } else {
            ReflectUtil.setValue(entity, getPrimaryPropertyName(), UUID.randomUUID().toString().replaceAll("-", ""));
            return commonDao.add(getTableName(),entity);
        }
    }

    /**
     * 插入一个实体列表
     * @param list 实体列表
     * @return
     */
    public boolean add(List<T> list){
        list.stream().forEach(item -> {
            commonDao.add(getTableName(),item);
        });
        return true;
    }

    /**
     * 更新一个实体
     * @param entity
     * @return
     */
    public boolean update(T entity){
        return commonDao.update(getTableName(),entity);
    }

    /**
     * 更新一个对象，可以设置更新空值
     * @param entity
     * @param updateNullProperties
     * @return
     */
    public boolean update(T entity,String[] updateNullProperties){
        return commonDao.update(getTableName(),entity,updateNullProperties);
    }

    /**
     * 删除一个实体
     * @param entity
     * @return
     */
    public boolean delete(T entity){
        String id = (String) ReflectUtil.getValue(entity, getPrimaryPropertyName());
        return commonDao.delete(getTableName(),id);
    }

    /**
     * 删除一个实体
     * @param id
     * @return
     */
    public boolean delete(String id){
        return commonDao.delete(getTableName(),id);
    }


    /**
     * 删除一个实体
     * @param property 属性名
     * @param value 属性值
     * @return
     */
    public boolean delete(String property,Object value){
        return commonDao.delete(getTableName(),property,value);
    }

    /**
     * 删除一个实体
     * @param property 属性名
     * @param value 属性值
     * @return
     */
    public boolean deleteTrue(String property,Object value){
        return commonDao.deleteTrue(getTableName(),property,value);
    }

    /**
     * 删除一个实体
     * @param properties 属性名
     * @param values 属性值
     * @return
     */
    public boolean deleteTrue(String[] properties, Object[] values){
        return commonDao.deleteTrue(getTableName(),properties,values);
    }

    /**
     * 删除一个实体
     * @param properties 属性名数组
     * @param values 属性值数组
     * @return
     */
    public boolean delete(String[] properties,Object[] values){
        return commonDao.delete(getTableName(),properties,values);
    }

    /**
     * 删除多个实体
     * @param ids 属性名数组
     * @return
     */
    public boolean delete(List<String> ids){
        return commonDao.delete(getTableName(),ids);
    }

    /**
     * 根据主键查询实体
     * @param id
     * @return
     */
    public T getEntity(String id){
        return this.getEntity(getPrimaryName(),id);
    }

    /**
     * 根据单一属性查询实体
     * @param property
     * @param value
     * @return
     */
    public T getEntity(String property,Object value){
        return this.getEntity(new String[]{property},new Object[]{value});
    }

    /**
     * 根据多个属生查询实体
     * @param properties
     * @param values
     * @return
     */
    public T getEntity(String[] properties,Object[] values){

        if(commonDao.isNeedComId(getTableName())){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = commonDao.getComId();
            values = newValues;
        }

        String sql = "select * from " + getTableName() + " where is_delete = '0' ";

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }
        sqlLog.debug("getEntity",sql,values);
        List<T> list = (List<T>)jdbcTemplate.query(sql,new DefaultRowMapper(entityClass, this.getActualNameHandler()),values);
        if(list.size() > 0){
            return list.get(0);
        }else{
            return null;
        }
    }

    /**
     * 根据主键查询实体，忽略逻辑删除
     * @param id
     * @return
     */
    public T getRealEntity(String id){
        return this.getRealEntity(getPrimaryName(),id);
    }

    /**
     * 根据单一属性查询实体，忽略逻辑删除
     * @param property
     * @param value
     * @return
     */
    public T getRealEntity(String property,Object value){
        return this.getRealEntity(new String[]{property},new Object[]{value});
    }

    /**
     * 根据多个属生查询实体，忽略逻辑删除
     * @param properties
     * @param values
     * @return
     */
    public T getRealEntity(String[] properties,Object[] values){

        if(commonDao.isNeedComId(getTableName())){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = commonDao.getComId();
            values = newValues;
        }

        String sql = "select * from " + getTableName() + " where 1 = 1 ";

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            sql += " and " + properties[index] +" = ? ";
        }
        sqlLog.debug("getEntity",sql,values);
        List<T> list = (List<T>)jdbcTemplate.query(sql,new DefaultRowMapper(entityClass, this.getActualNameHandler()),values);
        if(list.size() > 0){
            return list.get(0);
        }else{
            return null;
        }
    }

    /**
     * 根据自定义sql名称查询实体
     * @param sqlName sql名称
     * @param values 参数值
     * @return
     */
    public T getEntityBySqlName(String sqlName,Object... values){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        return this.getEntityBySql(sql,values);
    }

    /**
     * 根据自定义sql查询实体
     * @param sql Sql语句
     * @param values 参数值
     * @return
     */
    public T getEntityBySql(String sql,Object... values){
        List<T> list = (List<T>)jdbcTemplate.query(sql,new DefaultRowMapper(entityClass, this.getActualNameHandler()),values);
        if(list.size() > 0){
            return list.get(0);
        }else{
            return null;
        }
    }

    /**
     * 查询单个ViewModel
     * @param sqlName
     * @param clazz
     * @param values
     * @return
     */
    public <M> M getViewModel(String sqlName, Class<M> clazz, Object[] values) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        List<M> list = jdbcTemplate.query(sql,new DefaultRowMapper(clazz, this.getActualNameHandler()),values);

        if(list.size() > 0){
            return list.get(0);
        }else{
            return null;
        }
    }

    /**
     * 查询单个ViewModelBySQL
     * @param sql
     * @param clazz
     * @param values
     * @return
     */
    public <M> M getViewModelBySQL(String sql, Class<M> clazz, Object[] values) {
        List<M> list = jdbcTemplate.query(sql,new DefaultRowMapper(clazz, this.getActualNameHandler()),values);

        if(list.size() > 0){
            return list.get(0);
        }else{
            return null;
        }
    }
    /**
     * 查询所有列表
     * @return
     */
    public List<T> getAll(){
        return this.queryList(new String[]{},new Object[]{},"=");
    }

    /**
     * 根据单一属性查询列表
     * @param property 属性
     * @param value 值
     * @return
     */
    public List<T> getList(String property,Object value){
        return this.queryList(new String[]{property},new Object[]{value},"=");
    }

    /**
     * 根据单一属性查询列表，并指定单个排序条件
     * @param property 属性
     * @param value 值
     * @param orderName 排序属性
     * @param sortOn 降序或升序
     * @return
     */
    public List<T> getList(String property,Object value,String orderName,String sortOn){
        return this.queryList(new String[]{property},new Object[]{value},new String[]{orderName},new String[]{sortOn},"=");
    }


    /**
     * 根据多个属性查询列表
     * @param properties 属性数组
     * @param values 值数组
     * @return
     */
    public List<T> getList(String[] properties,Object[] values){
        return this.queryList(properties,values,new String[]{},new String[]{},"=");
    }

    /**
     * 根据多个属性查询列表并根据ID转换成Map
     * @param properties 属性数组
     * @param values 值数组
     * @return
     */
    public Map<String,T> getListToMap(String[] properties, Object[] values){
        List<T> list = this.queryList(properties,values,new String[]{},new String[]{},"=");

        Map<String,T> map = new Hashtable<>();
        for(T entity : list){
            map.put((String) ReflectUtil.getValue(entity, getPrimaryPropertyName()),entity);
        }

        return map;
    }

    /**
     * 根据多个属性查询列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public List<T> getList(String[] properties,Object[] values,String[] orderNames,String[] sortOns){
        return this.queryList(properties,values,orderNames,sortOns,"=");
    }

    /**
     * 根据单一属性查询列表
     * @param property 属性
     * @param value 值
     * @return
     */
    public List<T> getListByLike(String property,Object value){
        return this.queryList(new String[]{property},new Object[]{value},"like");
    }

    /**
     * 根据单一属性查询列表，并指定单个排序条件
     * @param property 属性
     * @param value 值
     * @param orderName 排序属性
     * @param sortOn 降序或升序
     * @return
     */
    public List<T> getListByLike(String property,Object value,String orderName,String sortOn){
        return this.queryList(new String[]{property},new Object[]{value},new String[]{orderName},new String[]{sortOn},"like");
    }


    /**
     * 根据多个属性查询列表
     * @param properties 属性数组
     * @param values 值数组
     * @return
     */
    public List<T> getListByLike(String[] properties,Object[] values){
        return this.queryList(properties,values,new String[]{},new String[]{},"like");
    }

    /**
     * 根据多个属性查询列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public List<T> getListByLike(String[] properties,Object[] values,String[] orderNames,String[] sortOns){
        return this.queryList(properties,values,orderNames,sortOns,"like");
    }

    /**
     * 根据多个属性查询列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public List<T> getListByLike(String[] properties,Object[] values,List<RangeEntity> rangs,String[] orderNames,String[] sortOns){
        return this.queryList(properties,values,rangs,orderNames,sortOns,"like");
    }

    /**
     * 根据单一属性查询列表
     * @param property 属性
     * @param value 值
     * @return
     */
    protected List<T> queryList(String property,Object value,String operator){
        return this.queryList(new String[]{property},new Object[]{value},operator);
    }

    /**
     * 根据单一属性查询列表，并指定单个排序条件
     * @param property 属性
     * @param value 值
     * @param orderName 排序属性
     * @param sortOn 降序或升序
     * @return
     */
    protected List<T> queryList(String property,Object value,String orderName,String sortOn,String operator){
        return this.queryList(new String[]{property},new Object[]{value},new String[]{orderName},new String[]{sortOn},operator);
    }


    /**
     * 根据多个属性查询列表
     * @param properties 属性数组
     * @param values 值数组
     * @return
     */
    protected List<T> queryList(String[] properties,Object[] values,String operator){
        return this.queryList(properties,values,new String[]{},new String[]{},operator);
    }

    /**
     * 根据多个属性查询列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    protected List<T> queryList(String[] properties,Object[] values,String[] orderNames,String[] sortOns,String operator){
        return this.queryList(properties,values,new ArrayList<RangeEntity>(),orderNames,sortOns,operator);
    }
    /**
     * 根据多个属性查询列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    protected List<T> queryList(String[] properties,Object[] values,List<RangeEntity> ranges,String[] orderNames,String[] sortOns,String operator){

        if(commonDao.isNeedComId(getTableName())){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = commonDao.getComId();
            values = newValues;
        }

        String sql = "select * from " + getTableName() + " where is_delete = '0' ";
        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if ("like".equalsIgnoreCase(operator) && !StringUtil.getTrim(values[index]).contains("%")) {
                sql += " and " + properties[index] + " = ? ";
            } else {
                sql += " and " + properties[index] + " " + operator + " ? ";
            }
        }

        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                sql += " and " + sqlRange.getSql();
            }
        }

        if(orderNames.length > 0){
            int start=0;
            for(int index = 0;index < orderNames.length;index ++){

                Object property = orderNames[index];
                if(property == null || property.equals(""))
                    continue;

                if(start == 0)
                    sql += " order by ";

                if(start > 0)
                    sql += " , ";

                sql += property +" "+sortOns[index];
                start ++;
            }
        }

        List arrValues = Arrays.asList(values);
        List list = new ArrayList(arrValues);

        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                list.addAll(sqlRange.getList());
            }
        }
        sqlLog.debug("queryList",sql,values);
        return (List<T>) jdbcTemplate.query(sql,list.toArray(),new DefaultRowMapper(entityClass, this.getActualNameHandler()));
    }

    /**
     * 根据多个属性查询列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public List<T> queryList(String[] properties,Object[] values,List<RangeEntity> ranges,String[] orderNames,String[] sortOns,int start,int limit){

        if(commonDao.isNeedComId(getTableName())){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = commonDao.getComId();
            values = newValues;
        }

        String sql = "select * from " + getTableName() + " where is_delete = '0' ";
        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if(StringUtil.getTrim(values[index]).contains("%")) {
                sql += " and " + properties[index] +" like ? ";
            }else{
                sql += " and " + properties[index] +" = ? ";
            }
        }

        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                sql += " and " + sqlRange.getSql();
            }
        }

        if(orderNames.length > 0){
            int startIndex = 0;
            for(int index = 0;index < orderNames.length;index ++){

                Object property = orderNames[index];
                if(property == null || property.equals(""))
                    continue;

                if(startIndex == 0)
                    sql += " order by ";

                if(startIndex > 0)
                    sql += " , ";

                sql += property +" "+sortOns[index];
                startIndex ++;
            }
        }

        sql += " limit ?,?";

        List arrValues = Arrays.asList(values);
        List list = new ArrayList(arrValues);

        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                list.addAll(sqlRange.getList());
            }
        }

        list.add(start);
        list.add(limit);

        sqlLog.debug("queryList",sql,values);

        return (List<T>) jdbcTemplate.query(sql,list.toArray(),new DefaultRowMapper(entityClass, this.getActualNameHandler()));
    }

    /**
     * 根据自定义sql名称查询列表
     * @param sqlName sql名称
     * @param values 参数值
     * @return
     */
    public List<T> queryListBySqlName(String sqlName, Object[] values){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        sqlLog.debug("queryListBySqlName", sqlName);
        return this.queryListBySql(sql, values);
    }

    /**
     * 根据自定义sql名称查询列表
     * @param sqlName sql名称
     * @param values 命名参数值
     * @return
     */
    public List<T> queryListByNpSqlName(String sqlName, Map<String, ?> values) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        sqlLog.debug("queryListByNpSqlName", sqlName);
        return this.queryListByNpSql(sql, values);
    }

    /**
     * 根据自定义sql查询列表
     * @param sql sql语句
     * @param values 参数值
     * @return
     */
    public List<T> queryListBySql(String sql,Object... values){
        sqlLog.debug("queryListBySql",sql,values);
        return (List<T>)jdbcTemplate.query(sql,new DefaultRowMapper(entityClass, this.getActualNameHandler()),values);
    }

    /**
     * 根据自定义sql查询列表
     * @param sql sql语句
     * @param values 命名参数值
     * @return
     */
    public List<T> queryListByNpSql(String sql, Map<String, ?> values) {
        sqlLog.debug("queryListByNpSql", sql, values);
        return (List<T>) npJdbcTemplate.query(sql, values, new DefaultRowMapper(entityClass, this.getActualNameHandler()));
    }

    /**
     * 根据自定义sql名称查询列表
     *
     * @param sqlName sql名称
     * @param clazz
     * @param values  参数值
     * @return
     */
    public <M> List<M> getViewModelList(String sqlName, Class<M> clazz, Object[] values) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        sqlLog.debug("getViewModelList", sqlName);
        return this.getViewModelListBySQL(sql, clazz, values);
    }

    /**
     * 根据自定义sql名称查询列表
     *
     * @param sqlName sql名称
     * @param clazz
     * @param values  参数值
     * @return
     */
    public <M> List<M> getViewModelListByNpSqlName(String sqlName, Class<M> clazz, Map<String, ?> values) {
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        sqlLog.debug("getViewModelListByNpSqlName", sqlName);
        return this.getViewModelListByNpSql(sql, clazz, values);
    }

    /**
     * 根据自定义sql查询列表
     *
     * @param sql sql
     * @param clazz
     * @param values  参数值
     * @return
     */
    public <M> List<M> getViewModelListBySQL(String sql, Class<M> clazz, Object[] values) {
        sqlLog.debug("getViewModelListBySQL", sql, values);
        return jdbcTemplate.query(sql, new DefaultRowMapper(clazz, this.getActualNameHandler()), values);
    }

    /**
     * 根据自定义sql查询列表
     *
     * @param sql    sql
     * @param clazz
     * @param values 命名参数值
     * @return
     */
    public <M> List<M> getViewModelListByNpSql(String sql, Class<M> clazz, Map<String, ?> values) {
        sqlLog.debug("getViewModelListByNpSql", sql, values);
        return npJdbcTemplate.query(sql, values, new DefaultRowMapper(clazz, this.getActualNameHandler()));
    }

    /**
     * 根据自定义sql查询列表
     * @param sql sql语句
     * @param values 参数值
     * @return
     */
    public List<T> queryListBySql(String sql,Class<T> clazz, Object[] values){
        sqlLog.debug("queryListBySql",sql,values);
        return (List<T>)jdbcTemplate.query(sql,new DefaultRowMapper(clazz, this.getActualNameHandler()),values);
    }

    /**
     * 根据单一属性查询分页列表，并指定单个排序条件
     * @param page  分页对象
     * @param property  属性名
     * @param value 值
     * @param orderName 排序属性数据
     * @param sortOn 升降序数组
     * @return
     */
    public Page<T> queryPage(Page page,String property, Object value, String orderName, String sortOn){
        return queryPage(page,new String[]{property},new Object[]{value},new String[]{orderName},new String[]{sortOn});
    }


    /**
     * 根据单一属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param property  属性名
     * @param value 值
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public Page<T> queryPage(Page page,String property, Object value, String[] orderNames, String[] sortOns){
        return queryPage(page,new String[]{property},new Object[]{value},orderNames,sortOns);
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public Page<T> queryPage(Page page,String[] properties, Object[] values, String[] orderNames, String[] sortOns){
        return queryPage(page, properties, values, orderNames, sortOns, "like");
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public Page<T> queryPage(Page page,String[] properties, Object[] values, String[] orderNames, String[] sortOns, String operator){

        if(commonDao.isNeedComId(getTableName())){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = commonDao.getComId();
            values = newValues;
        }

        String sql = "select * from " + getTableName() + " where is_delete = '0' ";
        String sqlAll = "select count(*) from " + getTableName() + " where is_delete = '0' ";

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if ("like".equalsIgnoreCase(operator) && !StringUtil.getTrim(values[index]).contains("%")) {
                sql += " and " + properties[index] + " = ? ";
                sqlAll += " and " + properties[index] + " = ? ";
            } else {
                sql += " and " + properties[index] + " " + operator + " ? ";
                sqlAll += " and " + properties[index] + " " + operator + " ? ";
            }
        }


        if(orderNames.length > 0){
            int start=0;
            for(int index = 0;index < orderNames.length;index ++){

                Object property = orderNames[index];
                if(property == null || property.equals(""))
                    continue;

                if(start == 0) {
                    sql += " order by ";
                    sqlAll += " order by ";
                }

                if(start > 0) {
                    sql += " , ";
                    sqlAll += " , ";
                }

                sql += property +" "+sortOns[index];
                sqlAll += property +" "+sortOns[index];
                start ++;
            }
        }

        int start = page.getPageNo();
        int limit = page.getPageSize();
        sql = sql + " limit ? , ? ";

        List arrValues = Arrays.asList(values);
        List list = new ArrayList(arrValues);

        list.add((start - 1) * limit);
        list.add(limit);

        sqlLog.debug("queryPage",sql,list);
        List<T> lst = (List<T>) jdbcTemplate.query(sql,list.toArray(),
                new DefaultRowMapper(entityClass, this.getActualNameHandler()));

        Page<T> returnPage = new Page<>();
        returnPage.setResult(lst);
        returnPage.setPageNo(start);
        returnPage.setPageSize(limit);

        sqlLog.debug("queryPage",sqlAll,values);

        returnPage.setTotalCount(jdbcTemplate.queryForObject(sqlAll,values, Long.class));
        return returnPage;
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param orProperties  or 条件属性数组
     * @param orValues or 条件值数组
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public Page<T> queryPage(Page page,String[] properties, Object[] values, String[] orProperties, Object[] orValues,String[] orderNames, String[] sortOns){

        if(commonDao.isNeedComId(getTableName())){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = commonDao.getComId();
            values = newValues;
        }

        String sql = "select * from " + getTableName() + " where is_delete = '0' ";
        String sqlAll = "select count(*) from " + getTableName() + " where is_delete = '0' ";

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if(StringUtil.getTrim(values[index]).contains("%")) {
                sql += " and " + properties[index] +" like ? ";
                sqlAll += " and " + properties[index] +" like ? ";
            }else{
                sql += " and " + properties[index] +" = ? ";
                sqlAll += " and " + properties[index] +" = ? ";
            }
        }

        len = orProperties.length;
        String orSql = "";
        if(orProperties.length > 0) {
            for (int index = 0; index < len; index++) {
                String operator = StringUtil.getTrim(orValues[index]).contains("%") ? "like" : "=";

                if(index == 0){
                    orSql = orProperties[index] + " "+operator+" ? ";
                }else {
                    orSql += " or " + orProperties[index] + " "+operator+" ? ";
                }
            }

            sql += " and ("+orSql+") ";
            sqlAll += " and ("+orSql+") ";
        }


        if(orderNames.length > 0){
            int start=0;
            for(int index = 0;index < orderNames.length;index ++){

                Object property = orderNames[index];
                if(property == null || property.equals(""))
                    continue;

                if(start == 0) {
                    sql += " order by ";
                    sqlAll += " order by ";
                }

                if(start > 0) {
                    sql += " , ";
                    sqlAll += " , ";
                }

                sql += property +" "+sortOns[index];
                sqlAll += property +" "+sortOns[index];
                start ++;
            }
        }

        int start = page.getPageNo();
        int limit = page.getPageSize();
        sql = sql + " limit ? , ? ";

        List arrValues = Arrays.asList(values);
        List list = new ArrayList(arrValues);

        if(orProperties.length > 0){
            list.addAll(new ArrayList(Arrays.asList(orValues)));
        }

        list.add((start - 1) * limit);
        list.add(limit);


        sqlLog.debug("queryPage",sql,list);
        List<T> lst = (List<T>) jdbcTemplate.query(sql,list.toArray(),
                new DefaultRowMapper(entityClass, this.getActualNameHandler()));

        Page<T> returnPage = new Page<>();
        returnPage.setResult(lst);
        returnPage.setPageNo(start);
        returnPage.setPageSize(limit);

        sqlLog.debug("queryPage",sqlAll,values);
        returnPage.setTotalCount(jdbcTemplate.queryForObject(sqlAll,values, Long.class));
        return returnPage;
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param range 范围条件对象
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public Page<T> queryPage(Page page, String[] properties, Object[] values, RangeEntity range,String[] orderNames, String[] sortOns){
        List<RangeEntity> ranges = new ArrayList<>();
        ranges.add(range);
        return queryPage(page,properties,values,new String[]{},new Object[]{},ranges,orderNames,sortOns);
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public Page<T> queryPage(Page page, String[] properties, Object[] values, String[] orProperties, Object[] orValues,List<RangeEntity> ranges,String[] orderNames, String[] sortOns){

        if(commonDao.isNeedComId(getTableName())){//云平台框架自动添加公司id

            String[] newProperties = new String[properties.length + 1];
            System.arraycopy(properties,0,newProperties,0,properties.length);
            newProperties[newProperties.length-1] = "com_id";
            properties = newProperties;

            Object[] newValues = new Object[values.length + 1];
            System.arraycopy(values,0,newValues,0,values.length);
            newValues[newValues.length-1] = commonDao.getComId();
            values = newValues;
        }

        String sql = "select * from " + getTableName() + " where is_delete = '0' ";
        String sqlAll = "select count(*) from " + getTableName() + " where is_delete = '0' ";

        int len = properties.length;
        for (int index = 0; index < len; index++) {

            if(StringUtil.getTrim(values[index]).contains("%")) {
                sql += " and " + properties[index] +" like ? ";
                sqlAll += " and " + properties[index] +" like ? ";
            }else{
                sql += " and " + properties[index] +" = ? ";
                sqlAll += " and " + properties[index] +" = ? ";
            }

        }

        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                sql += " and " + sqlRange.getSql();
                sqlAll += " and " + sqlRange.getSql();
            }
        }

        len = orProperties.length;
        String orSql = "";
        if(orProperties.length > 0) {
            for (int index = 0; index < len; index++) {
                String operator = StringUtil.getTrim(orValues[index]).contains("%") ? "like" : "=";
                if(index == 0){
                    orSql = orProperties[index] + " "+operator+" ? ";
                }else {
                    orSql += " or " + orProperties[index] + " "+operator+" ? ";
                }
            }

            sql += " and ("+orSql+") ";
            sqlAll += " and ("+orSql+") ";
        }

        if(orderNames.length > 0){
            int start=0;
            for(int index = 0;index < orderNames.length;index ++){

                Object property = orderNames[index];
                if(property == null || property.equals(""))
                    continue;

                if(start == 0) {
                    sql += " order by ";
                    sqlAll += " order by ";
                }

                if(start > 0) {
                    sql += " , ";
                    sqlAll += " , ";
                }

                sql += property +" "+sortOns[index];
                sqlAll += property +" "+sortOns[index];
                start ++;
            }
        }

        int start = page.getPageNo();
        int limit = page.getPageSize();
        sql = sql + " limit ? , ? ";

        List arrValues = Arrays.asList(values);
        List list = new ArrayList(arrValues);

        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                list.addAll(sqlRange.getList());
            }
        }

        if(orProperties.length > 0){
            list.addAll(new ArrayList(Arrays.asList(orValues)));
        }

        list.add((start - 1) * limit);
        list.add(limit);


        sqlLog.debug("queryPage",sql,list.toArray());
        List<T> lst = (List<T>) jdbcTemplate.query(sql,list.toArray(),
                new DefaultRowMapper(entityClass, this.getActualNameHandler()));

        Page<T> returnPage = new Page<>();
        returnPage.setResult(lst);
        returnPage.setPageNo(start);
        returnPage.setPageSize(limit);

        List arrCountValues = Arrays.asList(values);
        List listCount = new ArrayList(arrCountValues);
        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                listCount.addAll(sqlRange.getList());
            }
        }

        if(orProperties.length > 0){
            listCount.addAll(new ArrayList(Arrays.asList(orValues)));
        }

        returnPage.setTotalCount(jdbcTemplate.queryForObject(sqlAll,listCount.toArray(), Long.class));
        sqlLog.debug("getViewModelPageBySqlName",sqlAll,listCount.toArray());
        return returnPage;
    }

    /**
     * 根据自定义sql名称分页查询列表
     * @param page 分页对象
     * @param sqlName sql列表名称
     * @param sqlCountName sql总条数名称
     * @param values 参数值
     * @return
     */
    public Page<T> queryPageBySqlName(Page page,String sqlName,String sqlCountName,Object[] values){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        String sqlCount = SystemSQL.getInstance().getSystemSQL(sqlCountName);
        return this.queryPageBySql(page,sql,sqlCount,values);
    }

    /**
     * 根据自定义sql名称分页查询列表
     * @param page 分页对象
     * @param sql sql列表名称
     * @param sqlCount sql总条数名称
     * @param values 参数值
     * @return
     */
    public <M> Page<M> getViewModelPageBySql(Page page,String sql,String sqlCount,Class<M> clazz,Object[] values){

        int start = page.getPageNo();
        int limit = page.getPageSize();

        List arrValues = Arrays.asList(values);
        List list = new ArrayList(arrValues);

        list.add((start - 1) * limit);
        list.add(limit);

        sqlLog.debug("getViewModelPageBySqlName",sql,values);
        List lst = jdbcTemplate.query(sql,list.toArray(),new DefaultRowMapper(clazz, this.getActualNameHandler()));

        Page returnPage = new Page();
        returnPage.setResult(lst);
        returnPage.setPageNo(start);
        returnPage.setPageSize(limit);


        returnPage.setTotalCount(jdbcTemplate.queryForObject(sqlCount,values, Long.class));
        sqlLog.debug("getViewModelPageBySqlName",sqlCount,values);
        return returnPage;
    }

    /**
     * 根据自定义sql名称分页查询列表
     * @param page 分页对象
     * @param sqlName sql列表名称
     * @param sqlCountName sql总条数名称
     * @param values 参数值
     * @return
     */
    public <M> Page<M> getViewModelPageBySqlName(Page page,String sqlName,String sqlCountName,Class<M> clazz,Object[] values){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        String sqlCount = SystemSQL.getInstance().getSystemSQL(sqlCountName);

        return getViewModelPageBySql(page, sql, sqlCount, clazz, values);
    }

    /**
     * 根据自定义sql名称分页查询列表
     * @param page 分页对象
     * @param sqlName sql列表名称
     * @param sqlCountName sql总条数名称
     * @param conditionBuilder 参数值
     * @return
     */
    public <M> Page<M> getViewModelPageBySqlName(Page page,String sqlName,String sqlCountName,Class<M> clazz, ConditionBuilder conditionBuilder){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName).replace("[condition]", conditionBuilder.getSql());
        String sqlCount = SystemSQL.getInstance().getSystemSQL(sqlCountName).replace("[condition]", conditionBuilder.getSql());

        return getViewModelPageBySql(page, sql, sqlCount, clazz, conditionBuilder.getParams().toArray());
    }

    /**
     * 根据自定义sql名称分页查询列表
     * @param page 分页对象
     * @param sql 列表sql
     * @param sqlCount 总条数sql
     * @param values 参数值
     * @return
     */
    public Page<T> queryPageBySql(Page page,String sql,String sqlCount,Object[] values){

        int start = page.getPageNo();
        int limit = page.getPageSize();

        List arrValues = Arrays.asList(values);
        List list = new ArrayList(arrValues);

        list.add((start - 1) * limit);
        list.add(limit);

        sqlLog.debug("queryPageBySql",sql,list.toArray());
        List<T> lst = (List<T>) jdbcTemplate.query(sql,list.toArray(),
                new DefaultRowMapper(entityClass, this.getActualNameHandler()));

        Page<T> returnPage = new Page<>();
        returnPage.setResult(lst);
        returnPage.setPageNo(start);
        returnPage.setPageSize(limit);


        returnPage.setTotalCount(jdbcTemplate.queryForObject(sqlCount,values, Long.class));
        sqlLog.debug("queryPageBySql",sqlCount,values);
        return returnPage;
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public <M> Page<M> getCMSViewModelPageBySqlName(Page page,String sqlName,String sumSqlName,Class<M> clazz,String[] properties, Object[] values, List<RangeEntity> ranges,String[] orderNames, String[] sortOns){
        return getCMSViewModelPageBySqlName(page,sqlName,sumSqlName,clazz,properties,values,new String[]{},new Object[]{},ranges,orderNames,sortOns,new Object[]{},true);
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public <M> Page<M> getCMSViewModelPageBySqlName(Page page,String sqlName,String sumSqlName,Class<M> clazz,String[] properties, Object[] values,String[] orProperties, Object[] orValues, List<RangeEntity> ranges,String[] orderNames, String[] sortOns){
        return getCMSViewModelPageBySqlName(page,sqlName,sumSqlName,clazz,properties,values,orProperties,orValues,ranges,orderNames,sortOns,new Object[]{},true);
    }


    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public <M> Page<M> getCMSViewModelPageBySqlName(Page page,String sqlName,String sumSqlName,Class<M> clazz,String[] properties, Object[] values,String[] orProperties, Object[] orValues,List<RangeEntity> ranges,String[] orderNames, String[] sortOns,Object[] params,boolean paramsAfterCondition){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        String sqlAll = SystemSQL.getInstance().getSystemSQL(sumSqlName);
        return getCMSViewModelPageBySql(page, sql, sqlAll, clazz, properties, values, orProperties, orValues, ranges, orderNames, sortOns, params, paramsAfterCondition);
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param page  分页对象
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public <M> Page<M> getCMSViewModelPageBySql(Page page,String sql,String sqlAll,Class<M> clazz,String[] properties, Object[] values,String[] orProperties, Object[] orValues,List<RangeEntity> ranges,String[] orderNames, String[] sortOns,Object[] params,boolean paramsAfterCondition){

        String conditionSql = "";
        List conditionList = new ArrayList();

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if(null == values[index]){
                conditionSql += " and " + properties[index] +" is null ";
            }else{
                if(StringUtil.getTrim(values[index]).contains("%")) {
                    conditionSql += " and " + properties[index] + " like ? ";
                }else{
                    conditionSql += " and " + properties[index] + " = ? ";
                }
                conditionList.add(values[index]);
            }
        }

        len = orProperties.length;
        String orSql = "";
        if(orProperties.length > 0) {
            for (int index = 0; index < len; index++) {

                String operator = StringUtil.getTrim(orValues[index]).contains("%") ? "like" : "=";

                if(index == 0){
                    orSql = orProperties[index] + " "+operator+" ? ";
                }else {
                    orSql += " or " + orProperties[index] + " "+operator+" ? ";
                }
                conditionList.add(orValues[index]);
            }

            conditionSql += " and ("+orSql+") ";
        }

        String customSql = "";
        List customList = new ArrayList();
        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                customSql += " and " + sqlRange.getSql();
                customList.addAll(sqlRange.getList());
            }
        }



        sql = sql.replace("[custom]", customSql);
        sqlAll = sqlAll.replace("[custom]", customSql);

        if(!conditionSql.equals("")){
            conditionSql = " 1=1 "+conditionSql;
        }else{
            conditionSql = " 1=1 ";
        }



        sql = sql.replace("[condition]", conditionSql);
        sqlAll = sqlAll.replace("[condition]", conditionSql);

        if (orderNames.length > 0) {
            sql = sql.replace("[orderby]", " order by "+orderNames[0]+" "+sortOns[0]);
        }else{
            sql = sql.replace("[orderby]","");
        }

        List arrValues = Arrays.asList(params);
        List list = new ArrayList();

        if(paramsAfterCondition) {
            list.addAll(conditionList);
            list.addAll(customList);
            list.addAll(arrValues);
        }else{
            list.addAll(arrValues);
            list.addAll(conditionList);
            list.addAll(customList);

        }

        sqlLog.debug("getCMSViewModelPageBySqlName",sqlAll,list.toArray());
        long count = jdbcTemplate.queryForObject(sqlAll,list.toArray(), Long.class);

        int start = page.getPageNo();
        int limit = page.getPageSize();
        list.add((start - 1) * limit);
        list.add(limit);

        sqlLog.debug("getCMSViewModelPageBySqlName",sql,list.toArray());
        List<M> lst = jdbcTemplate.query(sql,list.toArray(),new DefaultRowMapper(clazz, this.getActualNameHandler()));

        Page<M> returnPage = new Page();
        returnPage.setResult(lst);
        returnPage.setPageNo(start);
        returnPage.setPageSize(limit);
        returnPage.setTotalCount(count);
        return returnPage;
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @param orderNames 排序属性数据
     * @param sortOns 升降序数组
     * @return
     */
    public <M> List<M> getCMSViewModelListBySqlName(String sqlName,Class<M> clazz,String[] properties, Object[] values, List<RangeEntity> ranges,String[] orderNames, String[] sortOns){
        return getCMSViewModelListBySqlName(sqlName,clazz,properties,values,ranges,orderNames,sortOns,new Object[]{},true);
    }

    public <M> List<M> getCMSViewModelListBySqlName(String sqlName,Class<M> clazz,String[] properties, Object[] values, List<RangeEntity> ranges,String[] orderNames, String[] sortOns,Object[] params){
        return getCMSViewModelListBySqlName(sqlName,clazz,properties,values,ranges,orderNames,sortOns,params,true);
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @return
     */
    public <M> List<M> getCMSViewModelListBySqlName(String sqlName,Class<M> clazz,String[] properties, Object[] values, List<RangeEntity> ranges,String[] orderNames, String[] sortOns,Object[] params,boolean paramsAfterCondition){

        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);

        String conditionSql = "";
        List conditionList = new ArrayList();

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if(StringUtil.getTrim(values[index]).contains("%")) {
                conditionSql += " and " + properties[index] + " like ? ";
            }else{
                conditionSql += " and " + properties[index] + " = ? ";
            }
            conditionList.add(values[index]);
        }

        String customSql = "";
        List customList = new ArrayList();
        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                customSql += " and " + sqlRange.getSql();
                customList.addAll(sqlRange.getList());
            }
        }



        sql = sql.replace("[custom]", customSql);

        if(!conditionSql.equals("")){
            conditionSql = " 1=1 "+conditionSql;
        }else{
            conditionSql = " 1=1 ";
        }
        sql = sql.replace("[condition]", conditionSql);

        if (orderNames.length > 0) {
            sql = sql.replace("[orderby]", " order by "+orderNames[0]+" "+sortOns[0]);
        }else{
            sql = sql.replace("[orderby]","");
        }

        List arrValues = Arrays.asList(params);
        List list = new ArrayList();
        if(paramsAfterCondition) {
            list.addAll(conditionList);
            list.addAll(customList);
            list.addAll(arrValues);
        }else{
            list.addAll(arrValues);
            list.addAll(conditionList);
            list.addAll(customList);

        }

        sqlLog.debug("getCMSViewModelPageBySqlName",sql,list.toArray());
        List<M> lst = jdbcTemplate.query(sql,list.toArray(),new DefaultRowMapper(clazz, this.getActualNameHandler()));
        return lst;
    }


    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @return
     */
    public <M> M getCMSViewModelBySqlName(String sqlName,Class<M> clazz,String[] properties, Object[] values, List<RangeEntity> ranges){
        return getCMSViewModelBySqlName(sqlName,clazz,properties,values,ranges,new Object[]{},false);
    }

    /**
     * 根据多个属性查询分页列表，并指定多个排序条件
     * @param properties  属性数组
     * @param values 值数组
     * @param ranges 范围条件对象
     * @return
     */
    public <M> M getCMSViewModelBySqlName(String sqlName,Class<M> clazz,String[] properties, Object[] values, List<RangeEntity> ranges,Object[] params,boolean paramsAfterCondition){

        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);

        String conditionSql = "";
        List conditionList = new ArrayList();

        int len = properties.length;
        for (int index = 0; index < len; index++) {
            if(StringUtil.getTrim(values[index]).contains("%")) {
                conditionSql += " and " + properties[index] + " like ? ";
            }else{
                conditionSql += " and " + properties[index] + " = ? ";
            }
            conditionList.add(values[index]);
        }

        String customSql = "";
        List customList = new ArrayList();
        for(RangeEntity range : ranges) {
            SqlRange sqlRange = range.toSqlRange();
            if (!sqlRange.getSql().equals("")) {
                customSql += " and " + sqlRange.getSql();
                customList.addAll(sqlRange.getList());
            }
        }



        sql = sql.replace("[custom]", customSql);

        if(!conditionSql.equals("")){
            conditionSql = " 1=1 "+conditionSql;
        }else{
            conditionSql = " 1=1 ";
        }
        sql = sql.replace("[condition]", conditionSql);

        List arrValues = Arrays.asList(params);

        List list = new ArrayList();

        if(paramsAfterCondition) {
            list.addAll(conditionList);
            list.addAll(customList);
            list.addAll(arrValues);
        }else{
            list.addAll(arrValues);
            list.addAll(conditionList);
            list.addAll(customList);

        }

        sqlLog.debug("getCMSViewModelPageBySqlName",sql,list.toArray());
        List<M> lst = jdbcTemplate.query(sql,list.toArray(),new DefaultRowMapper(clazz, this.getActualNameHandler()));
        if(lst.size() > 0){
            return lst.get(0);
        }else{
            return null;
        }
    }


    /**
     * 根据sql name查询Long类型结果
     * @param sqlName
     * @param values
     * @return
     */
    public Long queryCountBySqlName(String sqlName, Object[] values){
        return queryObjectBySqlName(sqlName, values, Long.class);
    }

    /**
     * 根据sql name查询泛型结果
     * @param sqlName
     * @param values
     * @return
     */
    public <T> T queryObjectBySqlName(String sqlName, Object[] values, Class<T> requiredType){
        String sql = SystemSQL.getInstance().getSystemSQL(sqlName);
        return jdbcTemplate.queryForObject(sql, values, requiredType);
    }

    /**
     * 根据sql name查询泛型结果
     * @param sql
     * @param values
     * @return
     */
    public <T> T queryObjectBySql(String sql, Object[] values, Class<T> requiredType){
        return jdbcTemplate.queryForObject(sql, values, requiredType);
    }

    /**
     * 查询最大的编码
     * @param column
     * @param prefix
     * @param bit
     * @return
     */
    public String getMaxCode(String column,String prefix,int bit){
        return commonDao.getMaxCode(getTableName(),column,prefix,bit);
    }

    /**
     * 查询最大的编码
     * @param column
     * @param property
     * @param prefix
     * @param bit
     * @return
     */
    public String getMaxCode(String column,String property,String prefix,int bit){
        return commonDao.getMaxCode(getTableName(),column,property,prefix,bit);
    }
}
